<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
	pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Student Status</title>
</head>
<body>
	Student Status :
	<%-- Import the java.sql package --%>
	<%@ page import="java.sql.*"%>
	<%-- -------- Open Connection Code -------- --%>
	<%
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;

		try {
			// Registering Postgresql JDBC driver with the DriverManager
			Class.forName("org.postgresql.Driver");

			// Open a connection to the database using DriverManager
			conn = DriverManager
					.getConnection("jdbc:postgresql://localhost/admissions?"
							+ "user=postgres&password=27428572");
	%>
	<%
		// Begin transaction
			//insert Address
			conn.setAutoCommit(false);
			Statement stmt = conn.createStatement();
			rs = stmt
					.executeQuery("SELECT user_id FROM user_status WHERE user_Name='"
							+ request.getAttribute("acctName") + "'");
			rs.next();
			int userId = rs.getInt(1);
			rs = stmt
					.executeQuery("SELECT student_status FROM student_status WHERE user_id='"
							+ userId + "'");
			rs.next();
			out.println(rs.getString(1));
			out.println("<br>");

			rs = stmt
					.executeQuery("SELECT student_id FROM user_link WHERE user_id='"
							+ userId + "'");
			rs.next();
			rs = stmt
					.executeQuery("SELECT id,address,spec,residence,citizenship,name FROM student WHERE id='"
							+ rs.getInt(1) + "'");
			rs.next();
			int id = rs.getInt(1);
			int addId = rs.getInt(2);
			int specId = rs.getInt(3);
			int resId = rs.getInt(4);
			int citId = rs.getInt(5);
			String name = rs.getString(6);
			rs = stmt.executeQuery("SELECT name FROM countries WHERE id='"
					+ resId + "'");
			rs.next();
			String res = rs.getString(1);
			rs = stmt.executeQuery("SELECT name FROM countries WHERE id='"
					+ citId + "'");
			rs.next();
			String cit = rs.getString(1);
			rs = stmt
					.executeQuery("SELECT name FROM specializations WHERE id='"
							+ specId + "'");
			rs.next();
			String spec = rs.getString(1);
	%>
	<p>
	Name :
	<%=name%>
	<p>
		Citizenship :
		<%=cit%>
	<p>
		Residence :
		<%=res%>
	<p>Address :
	<p>
		<%
			if (res.equals("United States")) {
					rs = stmt
							.executeQuery("SELECT street,city,areacode,zipcode,phoneno,state FROM addresses WHERE id='"
									+ addId + "'");
					rs.next();
					String street = rs.getString(1);
					String city = rs.getString(2);
					int areacode = rs.getInt(3);
					int zipcode = rs.getInt(4);
					int phoneno = rs.getInt(5);
					int stateId = rs.getInt(6);
					rs = stmt.executeQuery("SELECT name FROM states WHERE id='"
							+ stateId + "'");
					rs.next();
					String state = rs.getString(1);
		%>

		<%
			} else {
					rs = stmt
							.executeQuery("SELECT street,city,areacode,zipcode,phoneno,telecode FROM addresses WHERE id='"
									+ addId + "'");
					rs.next();
					String street = rs.getString(1);
					String city = rs.getString(2);
					int areacode = rs.getInt(3);
					int zipcode = rs.getInt(4);
					int phoneno = rs.getInt(5);
					int telecode = rs.getInt(6);
		%>
		Street :
		<%=street%>&nbsp; City :<%=city%>&nbsp; TeleCode:<%=telecode%>
	<p>
		AreaCode :
		<%=areacode%>&nbsp; ZipCode:
		<%=zipcode%>
	<p>
		Phone Number :
		<%=phoneno%>
	<p>
		Specialization :
		<%=spec%>
	<p>Degrees :
	<p>
		<%
			rs = stmt
							.executeQuery("SELECT name,location,university, gpa,date,title FROM degrees WHERE student='"
									+ id + "'");
					while (rs.next()) {
						int discipId = rs.getInt(1);
						int locationId = rs.getInt(2);
						int uniId = rs.getInt(3);
						int gpa = rs.getInt(4);
						int date = rs.getInt(5);
						Statement stmt2 = conn.createStatement();
						ResultSet rs2 = stmt2
								.executeQuery("SELECT name FROM disciplines WHERE id='"
										+ discipId + "'");
						rs2.next();
						String discip = rs2.getString(1);
						rs2 = stmt2
								.executeQuery("SELECT name FROM countries WHERE id='"
										+ locationId + "'");
						rs2.next();
						String loc = rs2.getString(1);
						rs2 = stmt2
								.executeQuery("SELECT name FROM universities WHERE id='"
										+ locationId + "'");
						rs2.next();
						String uni = rs2.getString(1);
		%>
		Discipline :
		<%=discip%>
	<p>
		Universities :<%=uni%>&nbsp;&nbsp; Location :
		<%=loc%>
	<p>

		Award Date :<%=date%>&nbsp;&nbsp; GPA :
		<%=gpa%>
		<%
			}
				}
				conn.setAutoCommit(true);
			}

			catch (SQLException e) {
				throw new RuntimeException(e);
			}

			finally {
				if (rs != null) {
					try {
						rs.close();
					} catch (SQLException e) {
					}
					rs = null;
				}
				if (pstmt != null) {
					try {
						pstmt.close();
					} catch (SQLException e) {
					}
					pstmt = null;
				}
				if (conn != null) {
					try {
						conn.close();
					} catch (SQLException e) {
					}
					conn = null;
				}

			}
		%>
	
</body>
</html>